package com.managertrade.util.strategy;

import com.managertrade.dto.vo.KlineWithIndicators;
import org.apache.poi.ss.usermodel.*;
import java.io.BufferedReader;
import java.io.FileReader;
import java.util.ArrayList;
import java.util.List;

public class ExcelKlineReader {

    public static List<KlineWithIndicators> readExcelToKlines(String filePath) throws Exception {
        List<KlineWithIndicators> list = new ArrayList<>();

        try (BufferedReader br = new BufferedReader(new FileReader(filePath))) {
            String line;
            boolean isHeader = true;

            while ((line = br.readLine()) != null) {
                // 跳过表头
                if (isHeader) {
                    isHeader = false;
                    continue;
                }

                // 按逗号切分
                String[] values = line.split(",");

                if (values.length < 18) continue; // 保底，防止列不够

                KlineWithIndicators k = new KlineWithIndicators();

                k.setSymbol(values[0]);
                k.setPeriod(values[1]);
                k.setUniqueId(values[2]);
                k.setDatetime(values[3]);
                k.setTimestamp(Long.parseLong(values[4]));
                k.setOpen(Double.parseDouble(values[5]));
                k.setHigh(Double.parseDouble(values[6]));
                k.setLow(Double.parseDouble(values[7]));
                k.setClose(Double.parseDouble(values[8]));
                k.setVolume(Double.parseDouble(values[9]));

                k.setEma7(parseNullable(values[10]));
                k.setEma21(parseNullable(values[11]));
                k.setMacd(parseNullable(values[12]));
                k.setSignal(parseNullable(values[13]));
                k.setHistogram(parseNullable(values[14]));
                k.setBollUpper(parseNullable(values[15]));
                k.setBollMiddle(parseNullable(values[16]));
                k.setBollLower(parseNullable(values[17]));

                list.add(k);
            }
        }

        return list;
    }
    // 辅助方法：安全解析 Double，防止空值/异常
    private static Double parseNullable(String val) {
        if (val == null || val.trim().isEmpty()) {
            return null;
        }
        try {
            return Double.parseDouble(val.trim());
        } catch (NumberFormatException e) {
            return null;
        }
    }
    // 读取数值（空单元格默认 0）
    private static double getNumeric(Cell cell) {
        if (cell == null) return 0;
        if (cell.getCellType() == CellType.NUMERIC) return cell.getNumericCellValue();
        if (cell.getCellType() == CellType.STRING) return Double.parseDouble(cell.getStringCellValue());
        return 0;
    }

    // 读取可空数值（空单元格返回 null）
    private static Double getNullableNumeric(Cell cell) {
        if (cell == null) return null;
        if (cell.getCellType() == CellType.NUMERIC) return cell.getNumericCellValue();
        if (cell.getCellType() == CellType.STRING && !cell.getStringCellValue().isEmpty()) {
            return Double.parseDouble(cell.getStringCellValue());
        }
        return null;
    }
}